package com.budwk.app.sqfw.services.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.text.StrFormatter;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.json.JSONUtil;
import com.budwk.app.common.config.AppException;
import com.budwk.app.sqfw.dto.SqfwRallySignDTO;
import com.budwk.app.sqfw.enums.DownLoadFileNameEnum;
import com.budwk.app.sqfw.models.*;
import com.budwk.app.sqfw.services.SqfwRallyForAdminService;
import com.budwk.app.sqfw.services.SqfwRallySignForAdminService;
import com.budwk.app.sqfw.vo.SqfwRallyVO;
import com.budwk.app.sqfwV2.dto.RallyCustomDTO;
import com.budwk.app.sqfwV2.dto.RallyPdfSaveDTO;
import com.budwk.app.sys.models.Sys_dict;
import com.budwk.app.sys.models.Sys_user;
import com.budwk.app.sys.services.SysUserService;
import com.budwk.app.util.poiUtils.CommonFormOfficeUtils;
import com.budwk.app.util.poiUtils.excelPictureConfigBO.ExcelFileCreateParamBO;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.common.result.ResultCode;
import com.budwk.starter.database.service.BaseServiceImpl;
import com.budwk.starter.security.utils.SecurityUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.Strings;
import org.apache.commons.lang3.ObjectUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @program: wk-mini-dev-env
 * @description: 社区活动服务实现
 * @author: LJT
 * @create: 2023-11-14 10:06
 **/
@IocBean(args = {"refer:dao"})
public class SqfwRallySignForAdminServiceImpl extends BaseServiceImpl<Sqfw_rally_sign> implements SqfwRallySignForAdminService {
    public SqfwRallySignForAdminServiceImpl(Dao dao) {
        super(dao);
    }

    @Inject
    private SysUserService sysUserService;

    @Inject
    private SqfwRallyForAdminService sqfwRallyForAdminService;

    private static final String AGE_MINORITY = "1";// 未成年（18岁以下）

    private static final String AGE_ADULT = "2";// 已成年（18岁以上）

    private static final String AGE_RETIRE = "3";// 已退休（女45岁 男50岁）

    /**
     * 连表查询报名活动信息
     *
     * @param bean
     * @return
     */
    public List<Sqfw_rally_sign> getRallySignInfoList(Sqfw_rally_sign bean, String queryAll, String queryTime, int pageNo, int pageSize) {
        Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
        if (!"直属".equals(sysUser.getProvince())) {
            bean.setProvince(sysUser.getProvince());
        }
        if (!"直属".equals(sysUser.getCity())) {
            bean.setCity(sysUser.getCity());
        }
        if (!"直属".equals(sysUser.getDistrict())) {
            bean.setDistrict(sysUser.getDistrict());
        }
        if (!"直属".equals(sysUser.getCounty())) {
            bean.setCounty(sysUser.getCounty());
        }
        if (!"直属".equals(sysUser.getCommunity())) {
            bean.setCommunity(sysUser.getCommunity());
        }
        String sqlStr = buildConditionForQueryToString(bean, queryAll, queryTime, pageNo, pageSize);
        Sql sql = Sqls.create(sqlStr);
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                List<Sqfw_rally_sign> resultSqfwRallySignList = new ArrayList<Sqfw_rally_sign>();
                while (rs.next()) {
                    Sqfw_rally_sign resultSqfwRallySign = new Sqfw_rally_sign();
                    for (int i = 0; i < columnCount; i++) {
                        resultSqfwRallySign.setId(rs.getString("id"));
                        resultSqfwRallySign.setRallyName(rs.getString("rally_name"));
                        resultSqfwRallySign.setName(rs.getString("people_name"));
                        resultSqfwRallySign.setCreateDate(rs.getDate("create_date"));
                        resultSqfwRallySign.setSpecialMark(rs.getString("special_mark"));
                        resultSqfwRallySign.setGender(rs.getString("gender"));
                        resultSqfwRallySign.setProvince(rs.getString("province"));
                        resultSqfwRallySign.setCity(rs.getString("city"));
                        resultSqfwRallySign.setDistrict(rs.getString("district"));
                        resultSqfwRallySign.setCounty(rs.getString("county"));
                        resultSqfwRallySign.setCommunity(rs.getString("community"));
                        resultSqfwRallySign.setPhone(rs.getString("phone"));
                        resultSqfwRallySign.setRallyReleaseDept(rs.getString("rally_release_dept"));
                        resultSqfwRallySign.setRallyIncludeAge(rs.getString("age"));
                        resultSqfwRallySign.setCardId(rs.getString("card_id"));
                        resultSqfwRallySign.setSignFalg(rs.getInt("sign_falg"));
                        resultSqfwRallySign.setInfoJson(rs.getString("info_json"));
                        resultSqfwRallySign.setRallyContentJson(rs.getString("rally_content_json"));
                    }
                    resultSqfwRallySignList.add(resultSqfwRallySign);
                }
                return resultSqfwRallySignList;
            }
        });
        execute(sql);
        return sql.getList(Sqfw_rally_sign.class);
    }


    /**
     * 获取pdfExcel下载Id列表
     *
     * @param bean
     * @param queryAll
     */
    @Override
    public List<String> getPdfExcelFileDownIdList(Sqfw_rally_sign bean, String queryAll, String queryTime) {
        String sql = buildConditionForQueryToString(bean, queryAll, queryTime, 0, 0);
        return getPdfExcelFileDownIdList(sql);
    }

    /**
     * 公共获取pdf和Excel下载列表id
     *
     * @param sqlStr
     * @return
     */
    public List<String> getPdfExcelFileDownIdList(String sqlStr) {
        Sql sql = Sqls.create(sqlStr);
        sql.setCallback((Connection con, ResultSet rs, Sql sqlItem) -> {
            List<String> list = Lists.newArrayList();
            while (rs.next()) {
                list.add(rs.getString("id"));
            }
            return list;
        });
        execute(sql);
        return sql.getList(String.class);
    }

    /**
     * 社区活动表并上传
     *
     * @param ids
     * @return
     */
    public String createRallyExcelAndUpload(List<String> ids) {
        if (ids.size() <= 0) {
            return null;
        }
        //生成excel表
        try {
            StringBuffer sb = new StringBuffer();
            for (String id : ids) {
                sb.append("'").append(id).append("',");
            }
            Sqfw_rally_sign sqfwRallySign = new Sqfw_rally_sign();
            sqfwRallySign.setIds(sb.toString().substring(1, sb.length() - 1));
            List<Sqfw_rally_sign> sqfwRallySigns = getRallySignInfoList(sqfwRallySign, null, null, 0, 0);
            Map<String, String> timeOutPhoneMap = checkSignTimeout(sqfwRallySigns);
            List<Map<String, Object>> dataList = Lists.newArrayList();
            SimpleDateFormat SDF_START = new SimpleDateFormat("yyyy-MM-dd");
            int visAndInsNumIndex = 0;
            for (Sqfw_rally_sign rallySign : sqfwRallySigns) {
                visAndInsNumIndex++;
                Map<String, Object> rowDataMap = Maps.newHashMap();
                rowDataMap.put("num", visAndInsNumIndex);
                rowDataMap.put("name", rallySign.getName());
                rowDataMap.put("gender", (StringUtils.isNotEmpty(rallySign.getGender()) ? rallySign.getGender() : "-"));
                rowDataMap.put("phone", rallySign.getPhone());
                rowDataMap.put("age", (StringUtils.isNotEmpty(rallySign.getRallyIncludeAge()) ? rallySign.getRallyIncludeAge() : "-"));
                rowDataMap.put("date", null != rallySign.getCreateDate() ? SDF_START.format(rallySign.getCreateDate()) : "-");
                rowDataMap.put("specialMark", (StringUtils.isNotEmpty(rallySign.getSpecialMark()) ? rallySign.getSpecialMark() : "-"));
                rowDataMap.put("province", rallySign.getProvince());
                rowDataMap.put("city", rallySign.getCity());
                rowDataMap.put("district", rallySign.getDistrict());
                rowDataMap.put("county", rallySign.getCounty());
                rowDataMap.put("community", rallySign.getCommunity());
                rowDataMap.put("rallyName", rallySign.getRallyName());
                rowDataMap.put("dept", rallySign.getRallyReleaseDept());
                if (ObjectUtil.isNotEmpty(rallySign.getSignFalg()) && 0 == rallySign.getSignFalg()) {
                    rowDataMap.put("signFalgString", "未签到");
                } else if (ObjectUtil.isNotEmpty(rallySign.getSignFalg()) && 1 == rallySign.getSignFalg()) {
                    rowDataMap.put("signFalgString", "已签到");
                }

                // 需求：检查动态生成字段，将动态字段放入excel “其他” 列中展示
                StringBuilder jsonbSB = new StringBuilder();
                if (StringUtils.isNotEmpty(rallySign.getInfoJson()) && StringUtils.isNotEmpty(rallySign.getRallyContentJson())) {
                    // 人员信息
                    RallyPdfSaveDTO rallyInfoJsonDTO = JSONUtil.toBean(rallySign.getInfoJson(), RallyPdfSaveDTO.class);
                    // 活动信息
                    RallyPdfSaveDTO rallyContentJsonDTO = JSONUtil.toBean(rallySign.getRallyContentJson(),RallyPdfSaveDTO.class);
                    Map<String, String> map = new HashMap<String, String>();
                    if (null != rallyContentJsonDTO.getCustom() && CollectionUtil.isNotEmpty(rallyContentJsonDTO.getCustom())) {
                        // 遍历活动表jsonb获取存在字段 deflag=1
                        rallyContentJsonDTO.getCustom().forEach(h -> {
                            if ("1".equals(h.getDelFlag())) {
                                map.put(h.getKey(), h.getKey());
                            }
                        });
                    }
                    if (null != rallyInfoJsonDTO.getCustom() && CollectionUtil.isNotEmpty(rallyInfoJsonDTO.getCustom())) {
                        // 遍历人员信息表，获取jsonb中的字段名称和字段值
                        rallyInfoJsonDTO.getCustom().forEach(h -> {
                            if (h.getKey().equals(map.get(h.getKey()))) {
                                String value = h.getDefaultVal();
                                if(StringUtils.isEmpty(value)) {
                                    value = "";
                                }
                                jsonbSB.append("").append(h.getName()).append(":").append(value).append(", ");
                            }
                        });
                    }
                    if (StringUtils.isNotEmpty(jsonbSB.toString())) {
                        rowDataMap.put("others", jsonbSB.substring(0, jsonbSB.length() - 2));
                    } else {
                        rowDataMap.put("others", "-");
                    }
                } else {
                    rowDataMap.put("others", "-");
                }

                // 需求：30天内未签到，未签到数量大于等于3，标记为”*“
                String remark = timeOutPhoneMap.get(rallySign.getPhone() + rallySign.getName());
                if (StringUtils.isNotEmpty(remark) && ObjectUtil.isNotEmpty(rallySign.getSignFalg()) && 0 == rallySign.getSignFalg()) {
                    rowDataMap.put("remark", remark);
                } else {
                    rowDataMap.put("remark", "-");
                }
                dataList.add(rowDataMap);
            }
            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("rallyTempForAdmin.xlsx");
            //String fileName = "活动报名表" + DateUtil.format(new Date(), "yyyyMMddHHmmss");
            //zhangp 2023-12-21 文件名称修改
            String fileName = DownLoadFileNameEnum.ACTIVITY_PERSON_INFO.getName() + DateUtil.format(new Date(), "yyyyMMddHHmmss");
            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(null);
            excelFileCreateParam.setDataList(dataList);
            excelFileCreateParam.setClazz(SqfwRallySignDTO.class);
            excelFileCreateParam.setFileName(fileName);
            return CommonFormOfficeUtils.createExcelAndUpload(excelFileCreateParam);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(StrFormatter.format("生成Excel表错误"));
        }
        return null;
    }

    /**
     * 获取查询条件 String类型
     *
     * @param bean
     * @return
     */
    private String buildConditionForQueryToString(Sqfw_rally_sign bean, String queryAll, String queryTime, int pageNo, int pageSize) {
        StringBuffer sb = new StringBuffer(" SELECT t3.* FROM (SELECT t1.id id, t1.rally_id rally_id, t1.name people_name, t1.phone phone, t1.community community, t1.create_date create_date, t1.special_mark special_mark, t1.gender gender, t1.card_id card_id, (SUBSTRING(TO_CHAR(NOW(), 'YYYY'), 1, 4)::int - SUBSTRING(t1.card_id,7,4)::int) age, t1.province province, t1.city city, t1.district district, t1.county county, t1.sign_falg sign_falg, t2.rally_name rally_name, t2.rally_release_dept rally_release_dept, t1.info_json info_json, t2.rally_content_json rally_content_json  FROM public.sqfw_rally_sign t1 LEFT JOIN public.sqfw_rally t2 ON t1.rally_id = t2.id) t3 WHERE 1 = 1 ");
        if (Strings.isNotBlank(queryAll)) {
            sb.append(" AND ( rally_name LIKE '%").append(queryAll).append("%'")
                    .append(" OR people_name LIKE '%").append(queryAll).append("%'")
                    .append(" OR special_mark LIKE '%").append(queryAll).append("%'")
                    .append(" OR gender LIKE '%").append(queryAll).append("%'")
                    .append(" OR province LIKE '%").append(queryAll).append("%'")
                    .append(" OR city LIKE '%").append(queryAll).append("%'")
                    .append(" OR district LIKE '%").append(queryAll).append("%'")
                    .append(" OR county LIKE '%").append(queryAll).append("%'")
                    .append(" OR community LIKE '%").append(queryAll).append("%'")
                    .append(" OR phone like '%").append(queryAll).append("%' ")
                    .append(" OR rally_release_dept LIKE '%").append(queryAll).append("%')");
        }
        if (Strings.isNotBlank(bean.getRallyIncludeAge())) {
            if (AGE_MINORITY.equals(bean.getRallyIncludeAge())) {
                sb.append(" AND age < '18' ");
            } else if (AGE_ADULT.equals(bean.getRallyIncludeAge())) {
                sb.append(" AND age >= '18' ");
            } else if (AGE_RETIRE.equals(bean.getRallyIncludeAge())) {
                sb.append(" AND ((age >= '45' AND gender = '女') OR (age >= '50' AND gender = '男'))");
            }
        }
        if (Strings.isNotBlank(bean.getPhone())) {
            sb.append(" AND phone like '%").append(bean.getPhone()).append("%' ");
        }
        if (Strings.isNotBlank(bean.getGender())) {
            sb.append(" AND gender = '").append(bean.getGender()).append("' ");
        }
        if (Strings.isNotBlank(bean.getId())) {
            sb.append(" AND rally_id = '").append(bean.getId()).append("' ");
        }
        if (Strings.isNotBlank(bean.getRallyName())) {
            sb.append(" AND rally_name LIKE '%").append(bean.getRallyName()).append("%'");
        }
        if (Strings.isNotBlank(bean.getName())) {
            sb.append(" AND people_name LIKE '%").append(bean.getName()).append("%'");
        }
        if (Strings.isNotBlank(bean.getSpecialMark())) {
            if ("正常".equals(bean.getSpecialMark())) {
                sb.append(" AND special_mark = '否'");
            } else {
                sb.append(" AND special_mark = '").append(bean.getSpecialMark()).append("'");
            }
        }
        if (Strings.isNotBlank(bean.getGender())) {
            sb.append(" AND gender LIKE '%").append(bean.getGender()).append("%'");
        }
        if (Strings.isNotBlank(bean.getProvince())) {
            sb.append(" AND province LIKE '%").append(bean.getProvince()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCity())) {
            sb.append(" AND city LIKE '%").append(bean.getCity()).append("%'");
        }
        if (Strings.isNotBlank(bean.getDistrict())) {
            sb.append(" AND district LIKE '%").append(bean.getDistrict()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCounty())) {
            sb.append(" AND county LIKE '%").append(bean.getCounty()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCommunity())) {
            sb.append(" AND community LIKE '%").append(bean.getCommunity()).append("%'");
        }
        if (Strings.isNotBlank(bean.getRallyReleaseDept())) {
            sb.append(" AND rally_release_dept LIKE '%").append(bean.getRallyReleaseDept()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCardId())) {
            sb.append(" AND card_id LIKE '%").append(bean.getCardId()).append("%'");
        }
        if (Strings.isNotBlank(bean.getIds())) {
            sb.append(" AND id in ('").append(bean.getIds()).append(") ");
        }
        if (ObjectUtil.isNotEmpty(bean.getSignFalg())) {
            sb.append(" AND sign_falg = '").append(bean.getSignFalg()).append("' ");
        }

        if (Strings.isNotBlank(queryTime)) {
            String time[] = queryTime.split(",");
            Date date1 = new Date();
            date1.setTime(Long.parseLong(time[0]));
            Date date2 = new Date();
            date2.setTime(Long.parseLong(time[1]));
            SimpleDateFormat SDF_START = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            sb.append(" AND create_date > '").append(SDF_START.format(date1)).append("' AND create_date < '").append(SDF_START.format(date2)).append("'");
        }
        sb.append(" ORDER BY create_date DESC");
        if (pageNo >= 1) {
            pageNo = (pageNo - 1) * pageSize;
        }
        if (0 != pageSize) {
            sb.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(pageNo);
        }
        return sb.toString();
    }

    /**
     * 根据类型下载模板
     *
     * @return
     */
    @Override
    public String getDownloadTemplateFormType() {
        try {
            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("rallyTemp.xlsx");
            String fileName = DownLoadFileNameEnum.ACTIVITY_PERSON_INFO.getName() + DateUtil.format(new Date(), "yyyyMMddHHmmss");
            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(null);
            excelFileCreateParam.setDataList(null);
            excelFileCreateParam.setClazz(null);
            excelFileCreateParam.setFileName(fileName);
            return CommonFormOfficeUtils.createExcelAndUpload(excelFileCreateParam);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(StrFormatter.format("生成Excel模板错误"));
        }
        return null;
    }

    /**
     * 数量统计
     *
     * @param bean
     * @param queryAll
     * @param queryTime
     * @param ids
     * @return
     */
    @Override
    public Map<String, Integer> count(Sqfw_rally_sign bean, String queryAll, String queryTime, List<String> ids) {
        int countExcel = 0;
        int countPdf = 0;
        Map<String, Integer> countMap = new HashMap<String, Integer>();
        if (ObjectUtil.isNotEmpty(ids)) {
            Cnd cnd = Cnd.NEW();
            cnd.and("rally_id", "in", ids);
            countExcel = count(cnd);
            countPdf = queryRallyReportsPdfColl(null, ids).size();
        } else {
            countExcel = getRallySignInfoList(bean, queryAll, queryTime, 0, 0).size();
            SqfwRallyVO sqfwRally = new SqfwRallyVO();
            BeanUtil.copyProperties(bean, sqfwRally);
            sqfwRally.setQueryAll(queryAll);
            sqfwRally.setQueryTime(queryTime);
            countPdf = queryRallyReportsPdfColl(sqfwRally, null).size();
        }
        countMap.put("rally_sign_excel", countExcel);
        countMap.put("rally_sum_pdf", countPdf);
        return countMap;
    }

    @Override
    public String generateRallyReportsPdfColl(SqfwRallyVO bean, List<String> ids) {
        // 查询出需要合并的pdf资源路径
        Map<String, String> pdfPathMap = queryRallyReportsPdfColl(bean, ids);
        // 获取所有Map的键集
        Set<String> pdfPathList = pdfPathMap.values().stream().collect(Collectors.toSet());
        if (ObjectUtil.isEmpty(pdfPathList)) {
            throw new AppException(ResultCode.NULL_DATA_ERROR.getMsg());
        }
        // 合并pdf返回URL
        String fileNameTemplate = "{}_{}";
        String fileNamePrefix = "活动总结集合";
        String fileName = StrFormatter.format(fileNameTemplate, fileNamePrefix, DateUtil.format(new Date(), "yyyyMMddHHmmss"));
        String pdfUrl = CommonFormOfficeUtils.createConvergePdfAndUpload(new ArrayList<>(pdfPathList), fileName);
        if (Strings.isNotBlank(pdfUrl) && pdfUrl.startsWith("/")) {
            pdfUrl = NutzIocUtils.getOssPrefix() + pdfUrl;
        }
        return pdfUrl;
    }

    /**
     * 查询活动总结信息
     *
     * @param bean
     * @param ids
     * @return
     */
    @Override
    public Map<String, String> queryRallyReportsPdfColl(SqfwRallyVO bean, List<String> ids) {
        // 查询出需要查询的pdf资源路径
        List<String> rallyIds = new ArrayList<String>();
        if (ObjectUtil.isNotEmpty(ids)) {
            rallyIds = ids;
        } else {
            Cnd cnd = sqfwRallyForAdminService.buildConditionForQueryToCnd(bean);
            rallyIds = sqfwRallyForAdminService.query(cnd).stream().map(Sqfw_rally::getId).collect(Collectors.toList());
        }
        if (rallyIds.size() <= 0) {
            return new HashMap<>();
        }
        StringBuffer sqlSub = new StringBuffer("SELECT doc_detail ->> 'rallyId' AS id, doc_detail ->> 'pdfFileUrl' AS pdfFileUrl  FROM  shequ.\"sqfw_report_file\" WHERE service_record_type='活动总结' ");
        StringBuffer sb = new StringBuffer();
        for (String rallyId : rallyIds) {
            sb.append("'").append(rallyId).append("',");
        }
        if (sb.length() > 0) {
            sqlSub.append(" AND doc_detail ->> 'rallyId' IN (").append(sb.substring(0, sb.length() - 1)).append(")");
        }
        Sql sql = Sqls.create(sqlSub.toString());
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                Map<String, String> queryRallIdMap = new HashMap<String, String>();
                while (rs.next()) {
                    String pdfFileUrl = rs.getString("pdfFileUrl");
                    if (StringUtils.isEmpty(pdfFileUrl)) {
                        continue;
                    }
                    queryRallIdMap.put(rs.getString("id"), rs.getString("pdfFileUrl"));
                }
                return queryRallIdMap;
            }
        });
        execute(sql);
        return sql.getObject(Map.class);
    }

    /**
     * 检查是否签到超时(用电话号码+姓名判断，产品说身份证不一定有)
     */
    public Map<String, String> checkSignTimeout(List<Sqfw_rally_sign> sqfwRallySigns) {
        if (ObjectUtils.isEmpty(sqfwRallySigns)) {
            return null;
        }
        // 号码条件
        Set<String> phones = sqfwRallySigns.stream().map(Sqfw_rally_sign::getPhone).collect(Collectors.toSet());
        StringBuffer sbPhones = new StringBuffer();
        for (String phone : phones) {
            sbPhones.append("'").append(phone).append("',");
        }
        if (sbPhones.length() <= 0) {
            return null;
        }
        String subPhoneString = sbPhones.substring(0, sbPhones.length() - 1);
        // 姓名条件
        Set<String> names = sqfwRallySigns.stream().map(Sqfw_rally_sign::getName).collect(Collectors.toSet());
        StringBuffer sbNames = new StringBuffer();
        for (String name : names) {
            sbNames.append("'").append(name).append("',");
        }
        if (sbNames.length() <= 0) {
            return null;
        }
        String subNameString = sbNames.substring(0, sbNames.length() - 1);
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT t1.phone phone, t1.name name FROM ( SELECT SUM(1) num, phone, name FROM public.\"sqfw_rally_sign\" WHERE create_date BETWEEN NOW() - INTERVAL '30 DAY' AND NOW() AND (sign_falg = 0 OR sign_falg IS NULL) AND phone IN (")
                .append(subPhoneString).append(") AND name IN (").append(subNameString).append(") GROUP BY phone, name) t1 WHERE t1.num > 2");
        Sql sql = Sqls.create(sbSql.toString());
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                Map<String, String> timeOutIds = new HashMap<String, String>();
                while (rs.next()) {
                    for (int i = 0; i < columnCount; i++) {
                        String phone = rs.getString("phone");
                        String name = rs.getString("name");
                        if (StringUtils.isNotEmpty(phone) && StringUtils.isNotEmpty(name)) {
                            timeOutIds.put(phone + name, "*");
                        }
                    }
                }
                return timeOutIds;
            }
        });
        execute(sql);
        return sql.getObject(Map.class);
    }
}
